/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.utility.model;

import com.tamnhat.entity.ContTemDetail;
import com.tamnhat.utility.entity.ServiceInfor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class ServiceInforModel {
    public static ArrayList<ServiceInfor> getAll(String phoneNumber) throws Exception{
        ArrayList<ServiceInfor> list = new ArrayList<ServiceInfor>();
        Connection cn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String strSQL = " select a.sdp_user_id,b.product_id,b.product_name,to_char(a.created_time,'dd-mm-yyyy hh24:mi:ss'),to_char(a.canceled_time,'dd-mm-yyyy hh24:mi:ss'),a.status,c.partner_name,a.user_id "
                + " from sdp_users a,sdp_products b,partners c"
                + " where (a.product_id = b.product_id(+)) and (a.partner_id = c.partner_id(+)) and a.sdp_user_id like '%'|| ? ||'%' "
                + " order by a.created_time";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(strSQL);
            
            pst.setString(1, phoneNumber);
            
            rs = pst.executeQuery();
            while (rs.next()) {
                ServiceInfor c = new ServiceInfor();
                c.setPhoneNumber(rs.getString(1));
                c.setProductId(rs.getString(2));
                c.setProductName(rs.getString(3));
                c.setCreateTime(rs.getString(4));
                c.setCancelTime(rs.getString(5));
                c.setStatus(rs.getString(6));
                c.setPartnerName(rs.getString(7));                
                try{
                    c.setUser_id(Integer.parseInt(rs.getString(8)));
                }catch(Exception e){
                    e.printStackTrace();
                    c.setUser_id(0);
                }
                list.add(c);
            }            
        } finally {
            Database.closeObject(rs);
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
        return list;
    }
}
